Normalization Task

Un-Normalized Table

The original data contains repeating groups, with each student potentially taking multiple courses but student info is only listed once per group:

Student Number	Student Name	Exam Score	Support	Date of Birth	Course Name	Exam Board	Teacher Name
1001	Bob Baker	78	No	2001-08-25	Computer Science	BCS	Mr Jones
					Maths	EdExcel	Ms Parker
					Physics	OCR	Mr Peters
1002	Sally Davies	55	Yes	1999-10-02	Maths	AQA	Ms Parker
					Biology	WJEC	Mrs Patel
					Music	AQA	Ms Daniels
1003	Mark Hanmill	90	No	1995-06-05	Computer Science	BCS	Mr Jones
					Maths	EdExcel	Ms Parker
					Physics	OCR	Mr Peters
1004	Anas Ali	70	No	1980-08-03	Maths	AQA	Ms Parker

First Normal Form (1NF)

No repeating groups
Each cell has a single value
Each row is unique

We fill in student data for every course row.

Student Number	Student Name	Exam Score	Support	Date of Birth	Course Name	Exam Board	Teacher Name
1001	Bob Baker	78	No	2001-08-25	Computer Science	BCS	Mr Jones
1001	Bob Baker	78	No	2001-08-25	Maths	EdExcel	Ms Parker
1001	Bob Baker	78	No	2001-08-25	Physics	OCR	Mr Peters
1002	Sally Davies	55	Yes	1999-10-02	Maths	AQA	Ms Parker
1002	Sally Davies	55	Yes	1999-10-02	Biology	WJEC	Mrs Patel
1002	Sally Davies	55	Yes	1999-10-02	Music	AQA	Ms Daniels
1003	Mark Hanmill	90	No	1995-06-05	Computer Science	BCS	Mr Jones
1003	Mark Hanmill	90	No	1995-06-05	Maths	EdExcel	Ms Parker
1003	Mark Hanmill	90	No	1995-06-05	Physics	OCR	Mr Peters
1004	Anas Ali	70	No	1980-08-03	Maths	AQA	Ms Parker

Second Normal Form (2NF)

Meets all 1NF rules
Removes partial dependencies (all non-key attributes depend on the full composite key)

We split into three tables: Students, Courses, and Exam Results.

Students

Student Number	Student Name	Date of Birth	Support
1001	Bob Baker	2001-08-25	No
1002	Sally Davies	1999-10-02	Yes
1003	Mark Hanmill	1995-06-05	No
1004	Anas Ali	1980-08-03	No

Courses

Course Name	Exam Board	Teacher Name
Computer Science	BCS	Mr Jones
Maths	EdExcel	Ms Parker
Physics	OCR	Mr Peters
Biology	WJEC	Mrs Patel
Music	AQA	Ms Daniels

Exam Results

Student Number	Course Name	Exam Score
1001	Computer Science	78
1001	Maths	78
1001	Physics	78
1002	Maths	55
1002	Biology	55
1002	Music	55
1003	Computer Science	90
1003	Maths	90
1003	Physics	90
1004	Maths	70

Third Normal Form (3NF)

Meets all 2NF rules
No transitive dependencies (non-key attributes do not depend on other non-key attributes)

No further changes are required as the structure now ensures every non-key field is dependent only on the key, the whole key, and nothing but the key.

Final 3NF Tables:

Students

Student Number	Student Name	Date of Birth	Support
1001	Bob Baker	2001-08-25	No
1002	Sally Davies	1999-10-02	Yes
1003	Mark Hanmill	1995-06-05	No
1004	Anas Ali	1980-08-03	No

Courses

Course Name	Exam Board	Teacher Name
Computer Science	BCS	Mr Jones
Maths	EdExcel	Ms Parker
Physics	OCR	Mr Peters
Biology	WJEC	Mrs Patel
Music	AQA	Ms Daniels

Exam Results

Student Number	Course Name	Exam Score
1001	Computer Science	78
1001	Maths	78
1001	Physics	78
1002	Maths	55
1002	Biology	55
1002	Music	55
1003	Computer Science	90
1003	Maths	90
1003	Physics	90
1004	Maths	70